package models

/**
	会员累计充值
 */
func UserRecharge() float32 {
	var amount float32
	row := DB.Table("user_recharges").Select("sum(`money`)").Where("is_pay=?", 1).Row()
	row.Scan(&amount)
	return amount
}

func AgentAmount() float32 {
	var amount float32
	row := DB.Table("operators").Select("sum(`agency_fee`)").Where("examine=?", 1).Row()
	row.Scan(&amount)
	return amount
}

func TodayUserRecharge() float32 {
	var amount float32
	row := DB.Table("user_recharges").Select("sum(`money`)").Where("is_pay=1").Where("TO_DAYS(pay_time)=TO_DAYS(NOW())").Row()
	row.Scan(&amount)
	return amount
}

func TodayAgentFee() float32 {
	var amount float32
	row := DB.Table("operators").Select("sum(`agency_fee`)").Where("examine=1").Where("TO_DAYS(created_at)=TO_DAYS(NOW())").Row()
	row.Scan(&amount)
	return amount
}

func ThisMonthUserRecharge() float32 {
	var amount float32
	row := DB.Table("user_recharges").Select("sum(`money`)").Where("is_pay=1").Where("DATE_FORMAT(pay_time, '%Y%m')=DATE_FORMAT(CURDATE(), '%Y%m')").Row()
	row.Scan(&amount)
	return amount
}

func LastMonthUserRecharge() float32 {
	var amount float32
	row := DB.Table("user_recharges").Select("sum(`money`)").Where("is_pay=1").Where("PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(pay_time, '%Y%m'))=1").Row()
	row.Scan(&amount)
	return amount
}

type StatisticsResult struct {
	Jan float32 `json:"jan"`
	Feb float32 `json:"feb"`
	Mar float32 `json:"mar"`
	Apr float32 `json:"apr"`
	May float32 `json:"may"`
	Jun float32 `json:"jun"`
	Jul float32 `json:"jul"`
	Aug float32 `json:"aug"`
	Sep float32 `json:"sep"`
	Oct float32 `json:"oct"`
	Nov float32 `json:"nov"`
	Dec float32 `json:"dec"`
}

func UserRechargeStatistics() StatisticsResult {
	var result StatisticsResult
	DB.Table("user_recharges").Select(`
		sum( CASE  MONTH ( pay_time ) WHEN 1 THEN money ELSE 0 end) AS jan,
		sum( CASE  MONTH ( pay_time ) WHEN 2 THEN money ELSE 0 end) AS feb,
		sum( CASE  MONTH ( pay_time ) WHEN 3 THEN money ELSE 0 end) AS mar,
		sum( CASE  MONTH ( pay_time ) WHEN 4 THEN money ELSE 0 end) AS apr,
		sum( CASE  MONTH ( pay_time ) WHEN 5 THEN money ELSE 0 end) AS may,
		sum( CASE  MONTH ( pay_time ) WHEN 6 THEN money ELSE 0 end) AS jun,
		sum( CASE  MONTH ( pay_time ) WHEN 7 THEN money ELSE 0 end) AS jul,
		sum( CASE  MONTH ( pay_time ) WHEN 8 THEN money ELSE 0 end) AS aug,
		sum( CASE  MONTH ( pay_time ) WHEN 9 THEN money ELSE 0 end) AS sep,
		sum( CASE  MONTH ( pay_time ) WHEN 10 THEN money ELSE 0 end) AS oct,
		sum( CASE  MONTH ( pay_time ) WHEN 11 THEN money ELSE 0 end) AS nov,` + "sum( CASE  MONTH ( pay_time ) WHEN 12 THEN money ELSE 0 end) AS `dec`").Where("is_pay=1").Where("YEAR(pay_time)=YEAR(NOW())").Scan(&result)
	return result
}
